USE [Weval test]
GO

/****** Object:  StoredProcedure [dbo].[CrnDetails]    Script Date: 7/15/2013 7:21:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[CrnDetails] @CRN int, @year int, @semester int
AS
BEGIN
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	
--DECLARE @CRN int = 30626, @semester int = 3, @year int = 2013, @testID int = 64529;
DECLARE @testID int = 
(
	SELECT DISTINCT TOP 1 T.ID
	FROM Tests T
		INNER JOIN SectionTests ST ON T.ID = ST.testID
		INNER JOIN Results R ON ST.deliveryID = R.deliveryID
		INNER JOIN CourseSections CS ON R.sectionID = CS.ID
			AND CS.bannerCRN = @CRN
			AND CS.year = @year
			AND CS.semester = @semester
			AND typeID = 5
);

DECLARE @instructorID  int= --Get instructor assigned to class/CRN in question
(
	SELECT DISTINCT TOP 1 ST.instructorID
	FROM SectionsTaught ST 
		INNER JOIN Results R ON R.sectionID = ST.sectionID
			AND (R.deleted = 0 OR R.deleted IS NULL)
		INNER JOIN CourseSections CS ON CS.ID = R.sectionID
			AND CS.bannerCRN = @CRN
			AND CS.year = @year
			AND CS.semester = @semester
);
DECLARE @subjectCode nvarchar(4) =
(
	SELECT TOP 1 subjectcode FROM CourseSections CS WHERE CS.bannerCRN = @CRN AND CS.year = @year AND cs.semester = @semester
);
DECLARE @class nvarchar(20)= --Ex: '1010'
(
	SELECT TOP 1 CS.courseNumber FROM CourseSections CS WHERE CS.bannerCRN = @CRN AND CS.year = @year AND cs.semester = @semester
);
WITH cteMain AS
(
	SELECT
		bannerCRN,
		Section,
		Semester,
		Block,
		Year,
		SubjectCode,
		CourseNumber,
		ST.instructorID,
		R.studentID,
		U.FirstName,
		U.LastName,
		U.Email,
		T.ID testID,
		T.testName 
		--SELECT * 
	FROM SectionsTaught ST 
		INNER JOIN Results R ON R.sectionID = ST.sectionID
			AND ST.instructorID = @InstructorID
			AND (R.deleted = 0 OR R.deleted IS NULL)
		INNER JOIN CourseSections CS ON CS.ID = R.sectionID
			AND CS.year = @year
			AND CS.bannerCRN = @CRN
			AND CS.semester = @semester
		INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
		INNER JOIN Questions Q ON Q.ID = QR.questionID
			AND Q.testID = @TestID
		INNER JOIN Tests T ON T.ID = Q.testID 
			AND T.typeID = 5 --Course Eval
		INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
		INNER JOIN Distractors D ON D.ID = DR.distractorID
		INNER JOIN Users U ON U.Id = ST.instructorID	
)


SELECT 
	COALESCE(subjectCode + courseNumber, '')  ClassString,
	bannerCRN,
	Section,
	Semester,
	CASE WHEN semester = 3 THEN 'Spring' WHEN semester = 2 THEN 'Fall' ELSE 'Summer' END SemesterString,
	Block,
	Year,
	SubjectCode,
	CourseNumber,
	instructorID,
	FirstName,
	LastName,
	Email,
	COUNT(*) responses,
	COUNT(DISTINCT studentID) students,
	testID,
	testName
FROM 
	cteMain
GROUP BY
	bannerCRN,
	Section,
	Semester,
	Block,
	Year,
	SubjectCode,
	CourseNumber,
	instructorID,
	FirstName,
	LastName,
	Email,
	testID,
	testName
END

GO

